import datetime
import lib.mysql as mysql
import lib.config as config
import lib.util as util


# 获取行程,全部、线上、线下
def getMainDataForRideOrder(category):
    sql = "select DATE_FORMAT(ride_order.updated_at, '%Y-%m-%d %H:%i') AS date_format_minute,ride.canton_id, ride.company_id, ride.is_imitate, SUM(ride_order.amount) AS value from ride_order join ride on ride_order.ride_id = ride.id  where ride_order.state = 2 and ride_order.refunded = 0 and ride_order.updated_at between '{}' and '{}' ".format(start_time, end_time)
    if category == 1:
        sql += " and ride_order.preset_through = 2 "
    elif category == 2:
        sql += " and ride_order.preset_through = 1 "
    sql += " group by ride.canton_id, ride.company_id, ride.is_imitate, date_format_minute"
    return db.getRows(sql)

def getMainDataForInfoServiceFee(category):
    sql = "select DATE_FORMAT(ride_order.updated_at, '%Y-%m-%d %H:%i') AS date_format_minute,ride.canton_id, ride.company_id, ride.is_imitate, COALESCE(SUM(ride_order.fare_paid - ride_order.amount),0) AS value from ride_order join ride on ride_order.ride_id = ride.id  where ride_order.state = 2 and ride_order.refunded = 0 and ride_order.updated_at between '{}' and '{}' and ride_order.fare_paid > ride_order.amount ".format(start_time, end_time)
    if category == 1:
        sql += " and ride_order.preset_through = 2 "
    elif category == 2:
        sql += " and ride_order.preset_through = 1 "
    sql += " group by ride.canton_id, ride.company_id, ride.is_imitate, date_format_minute"
    return db.getRows(sql)

def getMainDataForFreeInfoServiceFee(category):
    sql = "select DATE_FORMAT(ride_order.updated_at, '%Y-%m-%d %H:%i') AS date_format_minute,ride.canton_id, ride.company_id, ride.is_imitate, COALESCE(SUM(ride_order.info_service_amount),0) AS value from ride_order join ride on ride_order.ride_id = ride.id  where ride_order.state = 2 and ride_order.refunded = 0 and ride_order.updated_at between '{}' and '{}' and ride_order.fare_paid = ride_order.amount ".format(start_time, end_time)
    if category == 1:
        sql += " and ride_order.preset_through = 2 "
    elif category == 2:
        sql += " and ride_order.preset_through = 1 "
    sql += " group by ride.canton_id, ride.company_id, ride.is_imitate, date_format_minute"
    return db.getRows(sql)


# 获取空驶费 全部、线上
def getMainDataForCancellationOrder(category):
    sql = "select DATE_FORMAT(cancellation_order.updated_at, '%Y-%m-%d %H:%i') AS date_format_minute, ride.canton_id, ride.company_id, ride.is_imitate, SUM(cancellation_order.amount) as value from cancellation_order join ride_cancellation on cancellation_order.cancel_id = ride_cancellation.id join ride on ride_cancellation.ride_id = ride.id where cancellation_order.state = 2 and cancellation_order.refunded = 0 and ride.is_imitate in (0, 1) "
    sql += " and cancellation_order.updated_at between '{}' and '{}' group by ride.canton_id, ride.company_id, ride.is_imitate, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)


# 获取街边收款 全部、线上
def getMainDataForStreetRideOrder(category):
    sql = "select DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i') AS date_format_minute, canton_id, company_id, SUM(amount) AS value from street_ride_order where state = 2 and updated_at between '{}' and '{}' ".format(start_time, end_time)
    sql += " group by canton_id, company_id, date_format_minute"
    return db.getRows(sql)


#获取扫码行程-全部、线上
def getMainDataForScanRideOrder(category):
    sql = "select FROM_UNIXTIME(scan_ride_order.paid_timestamp, '%Y-%m-%d %H:%i') AS date_format_minute, scan_ride.canton_id, scan_ride.company_id, SUM(scan_ride_order.amount) AS value from scan_ride_order join scan_ride on scan_ride_order.scan_ride_id = scan_ride.id where scan_ride_order.is_paid = 1 and scan_ride_order.paid_timestamp between {} and {}".format(datetime.datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S").timestamp(), datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S").timestamp())
    sql += " group by scan_ride.canton_id, scan_ride.company_id, date_format_minute"
    return db.getRows(sql)


def getMainData():
    insert_data = []
    ride_a = getMainDataForRideOrder(0)
    ride_a = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 1, 'through_type': 0, 'is_imitate': d} for a,b,c,d,e in ride_a]
    ride_o = getMainDataForRideOrder(1)
    ride_o = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 1, 'through_type': 1, 'is_imitate': d} for a,b,c,d,e in ride_o]
    ride_f = getMainDataForRideOrder(2)
    ride_f = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 1, 'through_type': 2, 'is_imitate': d} for a,b,c,d,e in ride_f]
    c_a = getMainDataForCancellationOrder(0)
    c_a = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 2, 'through_type': 0, 'is_imitate': d} for a,b,c,d,e in c_a]
    c_o = getMainDataForCancellationOrder(1)
    c_o = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 2, 'through_type': 1, 'is_imitate': d} for a,b,c,d,e in c_o]
    s_a = getMainDataForStreetRideOrder(0)
    s_a = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': d, 'type': 3, 'through_type': 0, 'is_imitate': 0} for a,b,c,d in s_a]
    s_o = getMainDataForStreetRideOrder(1)
    s_o = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': d, 'type': 3, 'through_type': 1, 'is_imitate': 0} for a,b,c,d in s_o]
    ss_a = getMainDataForScanRideOrder(0)
    ss_a = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': d, 'type': 4, 'through_type': 0, 'is_imitate': 0} for a,b,c,d in ss_a]
    ss_o = getMainDataForScanRideOrder(1)
    ss_o = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': d, 'type': 4, 'through_type': 1, 'is_imitate': 0} for a,b,c,d in ss_o]
    info_a = getMainDataForInfoServiceFee(0)
    info_a = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 5, 'through_type': 0, 'is_imitate': d} for a,b,c,d,e in info_a]
    info_b = getMainDataForInfoServiceFee(1)
    info_b = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 5, 'through_type': 1, 'is_imitate': d} for a,b,c,d,e in info_b]
    info_c = getMainDataForInfoServiceFee(2)
    info_c = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 5, 'through_type': 2, 'is_imitate': d} for a,b,c,d,e in info_c]
    free_info_a = getMainDataForFreeInfoServiceFee(0)
    free_info_a = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 6, 'through_type': 0, 'is_imitate': d} for a,b,c,d,e in free_info_a]
    free_info_b = getMainDataForFreeInfoServiceFee(1)
    free_info_b = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 6, 'through_type': 1, 'is_imitate': d} for a,b,c,d,e in free_info_b]
    free_info_c = getMainDataForFreeInfoServiceFee(2)
    free_info_c = [{'canton_id': b, 'company_id': c, 'stats_date': a, 'value': e, 'type': 6, 'through_type': 2, 'is_imitate': d} for a,b,c,d,e in free_info_c]

    insert_data = [] + ride_a + ride_o + ride_f + c_a + c_o + s_a + s_o + ss_a + ss_o + info_a + info_b + info_c + free_info_a + free_info_b + free_info_c
    return insert_data


start_time = util.get_start_date()
end_time = util.get_end_date()

current_time = datetime.datetime.now()
if end_time is None:
    # 转为指定格式
    end_time = current_time.strftime("%Y-%m-%d %H:%M:%S")
else:
    end_time = f"{end_time} 23:59:59"

if start_time is None:
    start_time = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=10)
    start_time = start_time.strftime("%Y-%m-%d %H:%M:%S")
else:
    start_time = f"{start_time} 00:00:00"
current_time = current_time.strftime("%Y-%m-%d %H:%M:%S")


util.add_log(f"即将处理 {start_time} - {end_time} 的收入数据")
db = mysql.Db(config.config['mysql']['master'])
db1 = mysql.Db(config.config['mysql']['slave'])
call_data = getMainData()
call_size = len(call_data)

util.add_log(f"共有数据 {call_size}")
db1.cursor.execute("START TRANSACTION")
try:
    sql = "delete from dashboard_income_minute where (stats_date BETWEEN '{}' AND '{}') OR stats_date < DATE_SUB(NOW(), INTERVAL 3 MONTH)".format(start_time[:-2]+"00", end_time)
    db1.cursor.execute(sql)
    if call_size > 0:
        sql = "INSERT INTO `dashboard_income_minute` (`canton_id`, `company_id`, `stats_date`, `value`, `type`, `through_type`, `is_imitate`, `created_at`, `updated_at`) VALUES "
        for item in call_data:
            canton_id = item['canton_id'] if item['canton_id'] is not None else 'NULL'
            company_id = item['company_id'] if item['company_id'] is not None else 'NULL'
            sql += f"({canton_id}, {company_id}, '{item['stats_date']}', {item['value']}, {item['type']}, {item['through_type']}, {item['is_imitate']}, '{current_time}', '{current_time}'),"
        sql = sql.strip(',')
        db1.cursor.execute(sql)
    db1.conn.commit()
    util.add_log("处理完成")
except Exception as e:
    db1.conn.rollback()
    util.add_log(f"处理失败: {e}")
